"""
MySQL数据库连接管理器
"""
import logging
import mysql.connector
from mysql.connector import pooling, Error
from contextlib import contextmanager
from typing import Optional, Dict, Any, Generator
from .config import db_config, POOL_CONFIG

logger = logging.getLogger(__name__)

class DatabaseManager:
    """数据库连接管理器"""
    
    def __init__(self):
        self.pool: Optional[pooling.MySQLConnectionPool] = None
        self.config = db_config
        
    def initialize_pool(self) -> bool:
        """初始化连接池"""
        try:
            pool_config = {
                **self.config.to_dict(),
                **POOL_CONFIG
            }
            
            # 首先尝试连接到MySQL服务器（不指定数据库）
            test_config = pool_config.copy()
            test_config.pop('database', None)
            test_config.pop('pool_name', None)
            test_config.pop('pool_size', None)
            test_config.pop('pool_reset_session', None)
            
            # 测试连接
            test_conn = mysql.connector.connect(**test_config)
            test_conn.close()
            
            # 创建连接池
            pool_config['charset'] = 'utf8mb4'
            pool_config['collation'] = 'utf8mb4_unicode_ci'
            self.pool = pooling.MySQLConnectionPool(**pool_config)
            logger.info(f"数据库连接池初始化成功: {self.config.host}:{self.config.port}/{self.config.database}")
            return True
            
        except Error as e:
            if e.errno == 1049:  # 数据库不存在
                logger.info(f"数据库 {self.config.database} 不存在，正在创建...")
                return self._create_database_and_pool()
            else:
                logger.error(f"数据库连接池初始化失败: {e}")
                return False
        except Exception as e:
            logger.error(f"数据库连接池初始化失败: {e}")
            return False
    
    def _create_database_and_pool(self) -> bool:
        """创建数据库并初始化连接池"""
        try:
            # 连接到MySQL服务器（不指定数据库）
            temp_config = self.config.to_dict()
            temp_config.pop('database')
            
            conn = mysql.connector.connect(**temp_config)
            cursor = conn.cursor()
            
            # 创建数据库
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.config.database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            conn.commit()
            cursor.close()
            conn.close()
            
            logger.info(f"数据库 {self.config.database} 创建成功")
            
            # 重新初始化连接池
            pool_config = {
                **self.config.to_dict(),
                **POOL_CONFIG
            }
            self.pool = pooling.MySQLConnectionPool(**pool_config)
            logger.info("数据库连接池重新初始化成功")
            return True
            
        except Error as e:
            logger.error(f"创建数据库失败: {e}")
            return False
    
    @contextmanager
    def get_connection(self) -> Generator[mysql.connector.MySQLConnection, None, None]:
        """获取数据库连接的上下文管理器"""
        if not self.pool:
            if not self.initialize_pool():
                raise RuntimeError("数据库连接池未初始化")
        
        connection = None
        try:
            connection = self.pool.get_connection()
            yield connection
        except Error as e:
            logger.error(f"数据库连接错误: {e}")
            if connection:
                connection.rollback()
            raise
        finally:
            if connection and connection.is_connected():
                connection.close()
    
    @contextmanager
    def get_cursor(self, dictionary=True) -> Generator[mysql.connector.cursor.MySQLCursor, None, None]:
        """获取游标的上下文管理器"""
        with self.get_connection() as conn:
            cursor = conn.cursor(dictionary=dictionary)
            try:
                yield cursor
                conn.commit()
            except Exception as e:
                conn.rollback()
                logger.error(f"数据库操作错误: {e}")
                raise
            finally:
                cursor.close()
    
    def execute_script(self, script_path: str) -> bool:
        """执行SQL脚本文件"""
        try:
            with open(script_path, 'r', encoding='utf-8') as file:
                script = file.read()
            
            # 改进的SQL语句分割逻辑
            statements = []
            lines = script.split('\n')
            current_statement = ""
            in_multiline_comment = False
            
            for line in lines:
                line = line.strip()
                
                # 跳过空行和单行注释
                if not line or line.startswith('--'):
                    continue
                
                # 处理多行注释开始
                if '/*' in line:
                    in_multiline_comment = True
                    continue
                
                # 处理多行注释结束
                if '*/' in line:
                    in_multiline_comment = False
                    continue
                
                # 跳过多行注释中的内容
                if in_multiline_comment:
                    continue
                
                # 跳过USE语句
                if line.upper().startswith('USE '):
                    continue
                
                current_statement += line + " "
                
                # 如果语句以分号结尾，则完成一个语句
                if line.endswith(';'):
                    stmt = current_statement.strip()
                    if stmt and not stmt.startswith('--'):
                        statements.append(stmt)
                    current_statement = ""
            
            # 处理最后一个语句（如果没有分号结尾）
            if current_statement.strip() and not current_statement.strip().startswith('--'):
                statements.append(current_statement.strip())
            
            with self.get_connection() as conn:
                cursor = conn.cursor()
                for i, statement in enumerate(statements):
                    try:
                        logger.info(f"执行SQL语句 {i+1}/{len(statements)}: {statement[:50]}...")
                        cursor.execute(statement)
                        logger.info(f"SQL语句 {i+1} 执行成功")
                    except Exception as e:
                        # 如果是表已存在的错误，忽略它
                        if "already exists" in str(e).lower():
                            logger.warning(f"表已存在，跳过: {statement[:50]}...")
                            continue
                        # 如果是重复键错误，忽略它（INSERT IGNORE 应该处理这个）
                        elif "duplicate entry" in str(e).lower():
                            logger.warning(f"重复数据，跳过: {statement[:50]}...")
                            continue
                        # 如果是重复外键约束错误，忽略它
                        elif "duplicate foreign key constraint" in str(e).lower():
                            logger.warning(f"外键约束已存在，跳过: {statement[:50]}...")
                            continue
                        # 如果是数据库已存在的错误，忽略它
                        elif "database exists" in str(e).lower():
                            logger.warning(f"数据库已存在，跳过: {statement[:50]}...")
                            continue
                        else:
                            logger.error(f"SQL语句 {i+1} 执行失败: {e}")
                            logger.error(f"失败语句: {statement}")
                            raise e
                conn.commit()
                cursor.close()
            
            logger.info(f"SQL脚本执行成功: {script_path}")
            logger.info(f"总共执行了 {len(statements)} 条SQL语句")
            return True
            
        except Exception as e:
            logger.error(f"执行SQL脚本失败: {e}")
            raise  # 抛出异常，让调用者处理
    
    def execute(self, sql: str) -> bool:
        """执行单个SQL语句"""
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(sql)
                conn.commit()
                cursor.close()
            return True
        except Exception as e:
            logger.error(f"执行SQL语句失败: {e}")
            raise
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            with self.get_cursor() as cursor:
                cursor.execute("SELECT 1")
                result = cursor.fetchone()
                return result is not None
        except Exception as e:
            logger.error(f"数据库连接测试失败: {e}")
            return False
    
    def get_connection_info(self) -> Dict[str, Any]:
        """获取连接信息"""
        return {
            'host': self.config.host,
            'port': self.config.port,
            'database': self.config.database,
            'user': self.config.user,
            'pool_size': POOL_CONFIG['pool_size'],
            'pool_name': POOL_CONFIG['pool_name']
        }
    
    def close_pool(self):
        """关闭连接池"""
        if self.pool:
            try:
                # MySQL连接池没有直接的close方法，通过设置为None让GC处理
                self.pool = None
                logger.info("数据库连接池已关闭")
            except Exception as e:
                logger.error(f"关闭数据库连接池失败: {e}")

# 全局数据库管理器实例
db_manager = DatabaseManager()

# 便捷函数，用于向后兼容
@contextmanager
def get_database_connection():
    """获取数据库连接的便捷函数"""
    with db_manager.get_connection() as conn:
        yield conn
